package Test_2019_2_26.Test2;

import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JdbcUtil {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private Statement stmt = null;
    private static String url;
    private static String user;
    private static String password;
    private static int initCount = 10;// 初始化连接数
    private static int maxCount = 50;// 最大连接数
    private static int currentCount = 0;// 当前连接数
    // 数据库连接的容器（池）
    private static List<Connection> connectionPool = new ArrayList<Connection>();

    static {
        Properties prop = new Properties();
        try {
            InputStream is = JdbcUtil.class.getClassLoader()
                    .getResourceAsStream("Test_2019_2_26.properties");
            prop.load(is);
            // 注册驱动
            String driverClassName = prop.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            // 初始化一定数量的数据库连接
            for (int i = 0; i < initCount; i++) {
                connectionPool.add(createConnection());
                currentCount++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 创建数据库连接
    private static Connection createConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 获取数据库连接
    public static synchronized Connection getConnection() throws SQLException {
        Connection conn = null;
        // 池中存在空闲连接，则获取空闲连接，并将其从池中移除
        if (connectionPool.size() > 0) {
            conn = connectionPool.get(0);
            connectionPool.remove(0);
        } else {
            if (currentCount < maxCount) {
                // 连接数没有达到最大值，创建新连接供其使用
                conn = createConnection();
                currentCount++;
            } else {
                throw new SQLException("已没有空闲的数据库连接。。。");
            }
        }
        return conn;
    }

    // 释放资源
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        // 将数据库李连杰重新放置到池中（切记不可关闭）
        if (conn != null) {
            connectionPool.add(conn);
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 获得PreparedStatement
    private PreparedStatement getPrepareStatement(String sql) {
        Connection conn = createConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    // 将数据库中用户信息转为集合
    List<Student> Select() {
        List<Student> list = new ArrayList<Student>();
        String sql = "select no,name,birthday,introduce from student_test2";
        PreparedStatement pstat = getPrepareStatement(sql);
        try {
            ResultSet rs = pstat.executeQuery();
            Student bd;
            while (rs.next()) {
                bd = new Student();
                bd.setNo(rs.getString("no"));
                bd.setName(rs.getString("name"));
                bd.setBirthday(rs.getString("birthday"));
                bd.setIntroduce(rs.getString("introduce"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    // 添加数据
    public int add(Student stu) {
        int rlt = 0;
        try {
            conn = getConnection();
            String sql = "insert into student_test2(no,name,birthday,introduce) values(?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, stu.getNo());
            pstmt.setString(2, stu.getName());
            pstmt.setString(3, stu.getBirthday());
            pstmt.setString(4, stu.getIntroduce());
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rlt;
    }
}
